﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration;

namespace ZhiHuSpider
{
    public class SqlDBConnect
    {
        private SqlConnection conn = null;

        #region  //构造函数
        public SqlDBConnect()
        {
            string connectionString = "Data Source=WIN-KQDI4JISBLJ\\SQLEXPRESS;Initial Catalog=ZhiHuDB;Integrated Security=True";
            //string connectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["DBContext"].ToString();
            conn = new SqlConnection(connectionString);
        }
        #endregion

        #region //打开连接
        public void OpenDb()
        {
            if (conn.State != ConnectionState.Open)
            {
                try
                {
                    conn.Open();
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
            }
        }
        #endregion

        #region //关闭连接
        public void CloseDb()
        {
            if (!object.Equals(conn, null) && (conn.State != ConnectionState.Closed))
            {
                conn.Close();
            }
        }
        #endregion

        #region //释放连接
        public void Dispose()
        {
            if (conn != null)
            {
                conn.Dispose();
                conn = null;
            }
        }
        #endregion

        #region  //执行单条SQL(插入、更新、删除)
        /// <summary>
        /// 执行单条SQL(插入、更新、删除)
        /// </summary>
        /// <param name="sql_"></param>
        public void ExecuteNonQuery(string sql_)
        {
            try
            {
                OpenDb();
                SqlCommand cm = new SqlCommand(sql_, conn);
                cm.ExecuteNonQuery();
                cm.Dispose();
                cm = null;
                CloseDb();
            }
            catch (Exception e)
            {
                throw new Exception(e.ToString() + "  " + sql_);
            }
        }
        #endregion

        #region  //执行SQL返回首行首列的值
        /// <summary>
        /// 执行SQL返回首行首列的值,不存在返回""
        /// </summary>
        public string GetSingleVal(string sql_)
        {
            string RetStr = null;
            try
            {
                OpenDb();
                SqlCommand cm = new SqlCommand(sql_, conn);
                RetStr = cm.ExecuteScalar() == null ? "" : cm.ExecuteScalar().ToString();
                cm.Dispose();
                cm = null;
                CloseDb();
            }
            catch (Exception e)
            {
                throw new Exception(e.ToString() + ", " + sql_);
            }

            return RetStr;
        }
        #endregion

        #region  //判断是否存在对应的数据
        /// <summary>
        /// 根据SQL判断是否存在对应的数据
        /// </summary>
        public bool YNExistData(string sql_)
        {
            bool ynExist = false;
            try
            {
                DataTable dt = GetDataTable(sql_);
                if (dt != null && dt.Rows.Count > 0)
                    ynExist = true;
            }
            catch (Exception e)
            {
                throw new Exception(e.ToString() + ", " + sql_);
            }

            return ynExist;
        }
        #endregion

        #region  //执行SQL返回DataTable数据集
        /// <summary>
        /// 执行SQL返回DataTable数据集
        /// </summary>
        /// <param name="sql_"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql_)
        {
            if (sql_ == "")
                return null;
            DataTable dt = null;
            DataSet ds = null;
            try
            {
                OpenDb();
                SqlDataAdapter myad = new SqlDataAdapter(sql_, conn);
                ds = new DataSet();
                myad.Fill(ds);//用数据适配器填充数据集
                myad.Dispose();
                myad = null;
                CloseDb();

                if (ds.Tables.Count <= 0)
                    return null;
                dt = ds.Tables[0];
            }
            catch (Exception e)
            {
                throw new Exception(e.ToString() + "  " + sql_);
            }
            return dt;
        }
        #endregion

        #region //使用事务执行多条SQL(插入、更新、删除)
        /// <summary>
        /// 使用事务执行多条SQL(插入、更新、删除)
        /// </summary>
        /// <param name="sqls"></param>
        public void ExecTansaction(List<string> sqls)
        {
            if (sqls.Count == 0) return;

            OpenDb();

            // 启动一个事务。 
            SqlTransaction myTran = conn.BeginTransaction();
            // 为事务创建一个命令
            SqlCommand myCom = new SqlCommand();
            myCom.Connection = conn;
            myCom.Transaction = myTran;
            try
            {
                foreach (string sql in sqls)
                {
                    myCom.CommandText = sql;
                    myCom.ExecuteNonQuery();
                }
                myTran.Commit();//提交事务
            }
            catch (Exception Ex)
            {
                myTran.Rollback();

                //返回异常的错误信息 
                //MessageBox.Show("提交数据失败!\n" + Ex.ToString(), "异常信息", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                throw new Exception("提交数据失败!\n" + Ex.Message.ToString());
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion
    }
}
